<?php
/*
 * Contains the databaseconnection class
 * @package waterwheel
 *
*/
/*
 *  Copyright 2010 Andrew Rickmann
 *
 *  Licensed under the Apache License, Version 2.0 (the "License");
 *  you may not use this file except in compliance with the License.
 *  You may obtain a copy of the License at
 *
 *  http://www.apache.org/licenses/LICENSE-2.0
 *
 *  Unless required by applicable law or agreed to in writing, software
 *  distributed under the License is distributed on an "AS IS" BASIS,
 *  WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
 *  See the License for the specific language governing permissions and
 *  limitations under the License.
*/

/**
 * The database connection provides an interface to the database
 *
 * The database connection class obtains the connection data from the xml file
 * establishes a conection and performs any queries required.
 *
 * Requires MYSQL 5.1+
 *
 * @author Andrew Rickmann
 * 
 *
 */
class databaseconnection {

    /**
     * The instance of the instantiated version of this class
     *
     * @var databaseconnection
     */
    private static $instance;


    /**
     * A static variable which holds the database connection information after
     * It has been read in from an XML file.
     *
     * @var Array Holds the connection data
     */
    private $connectiondata = null;

    /**
     *
     * @var class holds the PDO connection class
     */
    private $connection = null;

    /**
     * The constructor checks to make sure connection data is available
     * If it is not it tries to get that connection data and make a connection.
     *
     * @todo make sure there is something in the request class to handle the error url.
     *
     *
     */
    private function __construct() {

	//get the connection information if necessary
	if ( is_null( $this->connectiondata ) ) {
	    $this->connectiondata = $this->getconnectiondata();
	}
	try {
	    //create the connection
	    $this->connection = new PDO('mysql:host='.$this->connectiondata['host'].';dbname='.$this->connectiondata['schema'], $this->connectiondata['username'], $this->connectiondata['password']);
	} catch (PDOException $e) {
	    throw new waterwheelexception('No database connection available.');
	}
    }

    /**
     * Return an instance of this class
     * 
     * @return databaseconnection
     */
    public static function singleton()
    {
        if (!isset(self::$instance)) {
            $c = __CLASS__;
            self::$instance = new $c;
        }

        return self::$instance;
    }

    /**
     * Tries to load the database connection data from connectiondata.xml
     * The XML file should be in the root application path and contain:
     * *host - The database IP address / URI
     * *schema - The database name
     * *username
     * *password
     *
     *
     * @return Array | bool the connection information or false if the data
     * could not be retrieved or if some of it is missing.
     */
    private function getconnectiondata() {
	//check the file exists
	if ( file_exists( APPLICATION_PATH . '/connectiondata.xml' ) ) {
	    //get the file contents
	    $filedata = file_get_contents(APPLICATION_PATH . '/connectiondata.xml');
	    //convert the file contents to simplexml for reading
	    if ( $xmlconnectiondata = new SimpleXMLElement($filedata) ) {
		//create the connection array
		$connectiondata = array();
		//get the host
		if ( $host = (string) $xmlconnectiondata->host ) {
		    $connectiondata['host'] = $host;
		}
		//get the schema
		if ( $schema = (string) $xmlconnectiondata->schema ) {
		    $connectiondata['schema'] = $schema;
		}
		//get the username
		if ( $username = (string) $xmlconnectiondata->username ) {
		    $connectiondata['username'] = $username;
		}
		//get the password
		if ( $password = (string) $xmlconnectiondata->password ) {
		    $connectiondata['password'] = $password;
		}
		//check we have the full compliment
		if ( count( $connectiondata ) == 4 ) {
		    return $connectiondata;
		}
	    }
	} else {
	    //if there is no connnection available then throw an exception
	    throw new waterwheelexception('No database connection information available.');
	}
    }

    /**
     * Executes a SQL command.
     *
     * @param String $sql
     * @return mixed Whatever the result of the SQL command was
     */
    public static function exec( $sql ) {

	//test for a failed connection
	if ( databaseconnection::singleton()->connection === false ) {
	    return false;
	}

	return databaseconnection::singleton()->connection->exec( $sql );
    }

    /**
     * Executes a sql query with extra options
     *
     * If the params are specified the SQL command can contain '?' in place of values
     * These values will be treated like strings and automatically quoted and escaped
     *
     * If caching is enabled the result of the query will be saved to a cache file
     * on the file system and any identical requests that are recieved within
     * the cache_length specified will be given the result of that initial query.
     *
     * The return value is an array of objects representing the database rows
     *
     * <code>
     * $database = new databaseconnection();
     * $results = $database->query( 'SELECT colname FROM tablename' );
     *
     * echo $results[0]->colname;
     * </code>
     *
     *
     * @param String $sql The SQL command to execute, note that parameters must be marked with a ? named parameters are not supported.
     * @param Array $params An array of parameters to insert into the query
     * @param Bool $cached True if the request should be cached
     * @param Int $cache_length The number of minutes the data should be cached for
     * @return Array An array of objects representing Rows.
     */
    public static function query( $sql , $params = array() ) {

	//test for a failed connection
	if ( databaseconnection::singleton()->connection === false ) {
	    return false;
	}


	//make sure $args are an array
	if ( !is_array($params) ) {
	    $params = array();
	}

	//prepare the query
	$prepared_query = databaseconnection::singleton()->connection->prepare( $sql );
	//execute the query with the params
	//todo bind each param individually after checking its type.

	//go throught the params and bind them according to type
	for( $p = 1; $p <= count($params); $p++ ){
	    if ( is_int( $params[$p] ) ){
		$prepared_query->bindValue( $p , $params[$p-1] , PDO::PARAM_INT );
	    } else {
		//in the absense of abother type treat as a string
		$prepared_query->bindValue( $p , $params[$p-1] , PDO::PARAM_STR );
	    }
	}

	$prepared_query->execute();

	try {
	    //get the results as an array.
	    //This used to be a class but I changed it.
	    $results = $prepared_query->fetchall();

	    //if the query was an insert get the new ID
	    if ( strpos($sql, 'INSERT') === 0 ){
		$results = databaseconnection::singleton()->connection->lastInsertId();
	    }

	} catch(Exception $e) {
	    $results = array();
	}

	return $results;
    }


    public function queryScalar( $sql , $params = array() ){
	$results = databaseconnection::singleton()->query( $sql , $params );
	return $results[0][0];
    }


    /**
     * Checks whether a table exists in the database
     *
     * @param String $tablename The name of the table to check
     * @return bool true if the table exists
     */
    public static function table_exists( $tablename ){

	$check_table_sql = "SELECT COUNT(*) as count FROM information_schema.tables WHERE table_schema = ? AND table_name = ?;";
	$check_table_sql_params = array();
	$check_table_sql_params[] = databaseconnection::singleton()->connectiondata['schema'];
	$check_table_sql_params[] = $tablename;

	$result = databaseconnection::singleton()->queryScalar($check_table_sql,$check_table_sql_params);

	return ( $result == 1 ) ? true : false;

    }


    /**
     * Returns an array of columns and column types for a given database table.
     * The array is in the format:
     * Array['name']['name'] - the name of the column
     * Array['name']['type'] - the type of column (data type and length)
     *
     * @param String $tablename the name of the table
     * @return Array an array of the columns
     */
    public static function table_columns( $tablename ){

	//check if the table exists
	if ( !databaseconnection::singleton()->table_exists($tablename) ){
	    return false;
	}
	
	//new blank array to hold the results
	$columns = array();

	//get the column data from the information sceham
	$column_sql = "SELECT `COLUMN_NAME`, `COLUMN_TYPE` FROM `information_schema`.`COLUMNS` WHERE `TABLE_NAME` = ?;";
	$column_sql_params = array( $tablename );
	$result = databaseconnection::singleton()->query( $column_sql , $column_sql_params );

	//convert result to an array
	foreach( $result as $r ){
	    $ca = array();
	    $ca['name'] = $r['COLUMN_NAME'];
	    $ca['type'] = $r['COLUMN_TYPE'];
	    $columns[$r['COLUMN_NAME']] = $ca;
	}

	return $columns;
    }

}
?>
